Imports System.Threading
Imports System.Data.SqlClient
Imports System.IO

Namespace DbConnection.Impl

    <GlobalAvalibleObjectImplementation(implementation_of:=GetType(IConnection), overrides_exsisting:=False, singleton:=True)>
    Public Class TBaseConnection
        Implements IConnection


        Private sqlconnection As SqlConnection = Nothing
        Private used As Boolean = False


        Protected connection_string As String


        ''' <summary>
        ''' initialise the connection.
        ''' retries five times before firing an exception in case of failure
        ''' this is convinient in case we reach the maximum number of connections in the pool
        ''' </summary>
        ''' <remarks></remarks>
        Private Sub setItUp()
            ' TRACE LOGGING REMOVED [Leave this comment to allow find replace re-addition of trace logging]

            Dim finalException As New Exception
            sqlconnection = New SqlConnection(connection_string)
            For retries As Integer = 0 To 5
                Try
                    sqlconnection.Open()
                    Exit Sub
                Catch ex As Exception
                    finalException = ex
                End Try
                Thread.Sleep(1000)
            Next
            Throw finalException
        End Sub

        Private Sub testConnection()
            ' TRACE LOGGING REMOVED [Leave this comment to allow find replace re-addition of trace logging]
            'just try to fire a query, 
            Dim sqlcommand As SqlCommand = sqlconnection.CreateCommand()
            sqlcommand.CommandText = "select top 1 * from sys.tables"

            Dim reader As SqlDataReader = sqlcommand.ExecuteReader()
            reader.Read() 'now it will crash!
            'it didn't!!
            reader.Close()
            reader = Nothing
            sqlcommand.Dispose()
            sqlcommand = Nothing
        End Sub

        Private Sub tryRecover()
            ' TRACE LOGGING REMOVED [Leave this comment to allow find replace re-addition of trace logging]
            'the only thing I know about recovering is to ClearAllPools
            'since this function is called only in case of problems
            'admins shall be notificated
            sqlconnection.ClearAllPools()
            Me.setItUp()
            testConnection()
        End Sub


        Public Sub testAndTryRecover()
            ' TRACE LOGGING REMOVED [Leave this comment to allow find replace re-addition of trace logging]
            Try
                testConnection()
            Catch ex As Exception
                tryRecover()
            End Try
        End Sub

        Private Sub checkFirstUse()
            ' TRACE LOGGING REMOVED [Leave this comment to allow find replace re-addition of trace logging]
            'if it is the first query the connection will be tested
            If Not used Then
                Me.setItUp()
                Me.testAndTryRecover()
                Me.used = True
            End If
        End Sub

#Region "executeQueryWithDifferent ammount or parameters"

        ''' <summary>
        ''' Executes a query with failover against the database with a number of parameters.
        ''' </summary>
        ''' <param name="query">SQL query to execute</param>
        ''' <param name="parameters">SQL Parameteres required.</param>
        ''' <returns>Query result datatable</returns>
        Public Function executeQuery(ByVal query As String, ByVal ParamArray parameters() As SqlParameter) As DataTable Implements IConnection.executeQuery
            ' TRACE LOGGING REMOVED [Leave this comment to allow find replace re-addition of trace logging]

            Me.checkFirstUse()

            ' Create the command
            Dim command As SqlCommand = sqlconnection.CreateCommand()

            ' Add any SQL parameters
            If parameters IsNot Nothing Then
                For Each par As SqlParameter In parameters
                    Dim copy As New SqlParameter(par.ParameterName, par.DbType)
                    copy.Value = par.Value
                    command.Parameters.Add(copy)
                Next
            End If

            ' Set the query
            command.CommandText = query
            Dim table As DataTable
            'with failover code

            ' a Try with out noresulet
            table = New DataTable
            Dim adapter As New SqlDataAdapter(command)

            adapter.Fill(table)



            Return table
        End Function

#End Region


#Region "excuteNonQuery with different ammount of paramenters"

        ''' <summary>
        ''' Executes a non-query with failover against the database with a number of parameters.
        ''' </summary>
        ''' <param name="query">SQL to execute</param>
        ''' <param name="parameters">SQL Parameteres required.</param>
        ''' <returns>Number of rows affected by the query.</returns>
        Public Function executeNonQuery(ByVal query As String, ByVal ParamArray parameters() As SqlParameter) As Integer Implements IConnection.executeNonQuery
            ' TRACE LOGGING REMOVED [Leave this comment to allow find replace re-addition of trace logging]

            Me.checkFirstUse()

            ' Create the command and set up the SQL
            Dim command As SqlCommand = sqlconnection.CreateCommand()
            command.CommandText = query
            ' Add SQL parameters as required
            If parameters IsNot Nothing Then
                For Each par As SqlParameter In parameters
                    'we make a copy so we can let the calling routine use the parameter again
                    Dim copy As New SqlParameter(par.ParameterName, par.DbType)
                    copy.Value = par.Value
                    command.Parameters.Add(copy)
                Next
            End If

            Try
                Return command.ExecuteNonQuery()
            Catch ex As Exception
                Throw
            End Try


        End Function

#End Region


#Region "excuteScalar with different ammount of paramenters"

        ''' <summary>
        ''' Executes a scalar query with failover against the database with a number of parameters.
        ''' </summary>
        ''' <param name="query">Scalar SQL query to execute</param>
        ''' <param name="parameters">SQL Parameteres required.</param>
        ''' <returns>Scalar query result</returns>
        Public Function executeScalar(ByVal query As String, ByVal ParamArray parameters() As SqlParameter) As Object Implements IConnection.executeScalar
            ' TRACE LOGGING REMOVED [Leave this comment to allow find replace re-addition of trace logging]
            ' Set up and create the command with the query
            Me.checkFirstUse()

            Dim command As SqlCommand = sqlconnection.CreateCommand()
            command.CommandText = query
            ' Add any parameters
            If parameters IsNot Nothing Then
                For Each par As SqlParameter In parameters
                    Dim copy As New SqlParameter(par.ParameterName, par.DbType)
                    copy.Value = par.Value
                    command.Parameters.Add(copy)
                Next
            End If

            Return command.ExecuteScalar()

        End Function

#End Region


#Region "excuteFileScript "

        ''' <summary>
        ''' Executes a non-query with failover against the database 
        ''' </summary>
        ''' <param name="filePath">file path to execute it's script</param>
        ''' <returns>Number of rows affected by the query.</returns>
        Public Function executeFileScript(ByVal filePath As String) As Integer Implements IConnection.executeFileScript
            ' TRACE LOGGING REMOVED [Leave this comment to allow find replace re-addition of trace logging]

            Me.checkFirstUse()

            ' Create the command and set up the SQL
            Dim command As SqlCommand = sqlconnection.CreateCommand()
            Dim inStream As Stream = New FileStream(filePath, IO.FileMode.Open)
            Dim Query As String = New StreamReader(inStream).ReadToEnd()

            command.CommandText = Query

            ' Run with failover
            Return command.ExecuteNonQuery()

        End Function

#End Region

        Public Sub close() Implements IConnection.close
            ' TRACE LOGGING REMOVED [Leave this comment to allow find replace re-addition of trace logging]
            If sqlconnection IsNot Nothing Then
                sqlconnection.Close()
            End If
        End Sub


        Public Sub New(ByVal connection_string As String)
            Me.connection_string = connection_string
        End Sub

        <GlobalAvalibleObjectConstructor()>
        Public Shared Function createDefaultInstance() As IConnection
            Dim conn As New TBaseConnection
            conn.connection_string = conn.getGlobalObject(Of MysteryConfiguration)().db_connection_string
            Return conn
        End Function


        Private Sub New()

        End Sub


    End Class

End Namespace

